<?php
/**
 * Albireo module
 *
 * @copyright  Copyright (c) 2010 Albireo Solutions
 * @package    Kernel
 */
namespace Celebrio\Database;

use \dibi;
use Nette\Object;
use Albireo\Mode;
use Albireo\PasswordHasher;

use \NotSupportedException;

/**
 * Managable module MSSQL
 *
 * @author     Albireo Solutions
 * @package    Kernel
 */
class MSSQL2005 extends Object implements IModule,IDatabaseModule {
    private $database;

    public function __construct() {
        throw new NotSupportedException("MSSQL extension not implemented yet");
        $this->database = new DatabaseModule();
    }

    public function initialize($settings = null) {
        //TODO: wtf if , options initialize
        if ($settings == null) {
            $settings = Environment::getConfig('database');
        } else {
            $options = $this->getOptions($settings);
        }

        try {
            dibi::connect($options);
        } catch (\DibiException $e) {
            Mode::setCriticalMode(_("Kernel is in Critical Mode due to Database system unavailability."));
        }
    }

    public function checkAvailability() {
        $available = false;
        if ( extension_loaded ( "sqlsrv" ) ) {
            $available = true;
        }
        return $available;
    }

    public function checkFunctionality($values) {
        try {
            $options = $this->getOptions($values);
            //unset($options["options"]["Database"]);
            // TODO: ojeb!!! What if you dont have permissions to get to the DB
            $options["options"]["Database"] = "master";
            dibi::connect($options);
            return true;
        } catch (\DibiException $e) {
            return false;
        }
    }

    private function getOptions($values) {
        //TODO: not sure whether port can be included as in documentation of sqlsrv
        $options = array ("driver" => $values['driver'], "host" => $values['host'],
                "options" => array(
                        "UID" => $values['username'],
                        "PWD" => $values['password'],
                        "Database" => $values['database'],
                        "LoginTimeout" => 5
                        //"ReturnDatesAsStrings"=>true
        ));
        return $options;
    }

    public function console($query) {
        return $this->database->console($query);
    }

    public function databaseExists($dbname) {
        $query = dibi::query("IF db_id(%s) IS NOT NULL
                SELECT 1
                ELSE
                SELECT 0",$dbname );
        if($query->fetchSingle()==0) {
            $query->free();
            return false;
        } else {
            $query->free();
            return true;
        }
    }

    public function tablesExist($dbname) {
        $query = dibi::query("IF EXISTS (SELECT 1
    FROM ".$dbname.".INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE'
    AND TABLE_NAME='users')
        SELECT 1
ELSE
        SELECT 0 ");
        if($query->fetchSingle()==0) {
            $query->free();
            return false;
        } else {
            $query->free();
            return true;
        }
    }

    public function createLogin ( $username, $pass, $newUser = true) {
        $password = PasswordHasher::databaseHash($username, $pass);
        if ($newUser) {
            dibi::nativeQuery("CREATE LOGIN \"$username\" WITH PASSWORD = '$password', CHECK_POLICY = OFF;");
            dibi::nativeQuery("CREATE USER \"$username\" FOR LOGIN \"$username\" WITH DEFAULT_SCHEMA = db_owner;");
            //for some reason it doesnt work when there are 3 native queries with exec after each other...
            dibi::nativeQuery("EXEC sp_addrolemember 'db_ddladmin', \"$username\"; EXEC sp_addrolemember 'db_datareader', \"$username\"; EXEC sp_addrolemember 'db_datawriter', \"$username\";");
            //dibi::nativeQuery("EXEC sp_addrolemember 'db_datareader', '$username';");
            //dibi::nativeQuery("EXEC sp_addrolemember 'db_datawriter', '$username';");
        } else {
            dibi::nativeQuery("ALTER LOGIN \"$username\" WITH PASSWORD = '$password', CHECK_POLICY = OFF;");
        }
    }

    public function deleteLogin ( $username ) {
        dibi::nativeQuery("DROP USER \"$username\";");
        dibi::nativeQuery("DROP LOGIN \"$username\";");
    }

    public function getModuleName() {
        return "mssql";
    }

    /* --------------- DataBase Functions (DBF) ------------------------ */
    public function dbfConcat($first, $second) {
        return $first . " + " . $second;
    }

    public function dbfSubstr($string, $from, $for = null) {
        if ($for === null) {
            return "substring(" . $string . ", " . $from . ", " . $this->dbfStrlen($string) . ")";
        } else {
            return "substring(" . $string . ", " . $from . ", " . $for . ")";
        }
    }

    public function dbfStrlen($string) {
        return "len(" . $string . ")";
    }
}